﻿using Dapper;
using Domain.Entity.System_operation;
using Domain.Entity.System_Ordermanagement;
using Domain.IRepository.System_operation;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.DependencyInjection;

namespace EFCore.Repository.System_operation
{
    [Dependency(ServiceLifetime.Scoped)]
    public class Signature_receiptRepository : ISignature_receiptRepository
    {
        public MyDbContext myDbContext { get; set; }

        public Signature_receiptRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }

        public int Count(string? Sign_time, string? value2, string? Ord_startaddress, string? Ord_destination)
        {
            string sql = "select [Ord_number],[Client_name],[Ord_startaddress],[Ord_destination],[Ord_type],[Shipments_addres],[Receiving_addres],Count," +
                "[Sign_num],[Sign_by],[Sign_time] from[dbo].[Signature_receipt] inner join [dbo].[Orders] on [dbo].[Orders].[Ord_id] = [dbo].[Signature_receipt].[Ord_id]";
            if (Sign_time != null && Sign_time != "")
            {
                string[] time1 = Sign_time.Split(',');
                sql += $" and [Sign_time]>='{time1[0]}' and [Sign_time]<='{time1[1]}'";
            }
            if (value2 != null && value2 != "")
            {
                sql += $"AND (Client_name LIKE '%{value2}%')";
            }
            if (Ord_startaddress != null && Ord_startaddress != "")
            {
                sql += $" and [Ord_startaddress] like '%{Ord_startaddress}%'";
            }
            if (Ord_destination != null && Ord_destination != "")
            {
                sql += $" and [Ord_destination] like '%{Ord_destination}%'";
            }
            int count = myDbContext.Database.GetDbConnection().Query<Signature_receipt>(sql).Count();
            return count;
        }

        public List<Signature_receipt> GetSignature_receipt()
        {
            /*string sql= "select [Bill_Id],[Wb_number],[Order_Id],[Customer],[Origin_place],[Det_place],[Is_arrival],[Pickup_address],[Delivery_address]," +
                "[Driver_name],[Car_number],[Bill_nums],[Bill_volume],[Bill_weight],[Bill_cost]," +
                "[Driver_phone],[Pickup_time],[Pickup_endtime],[Plan_time],[Reality_time],[PlanGet_time],[Reality_time] " +
                "from [dbo].[Arrival_meter] inner join [dbo].[Bill_ol] on [dbo].[Arrival_meter].Billol_Id=[dbo].[Bill_ol].Billol_Id";
            List<Arrival_meter> list = myDbContext.Database.GetDbConnection().Query<Arrival_meter>(sql).ToList();
            var ArList = list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return ArList;*/
            string sql = "SELECT * FROM [dbo].[Signature_receipt]";
            return myDbContext.Database.GetDbConnection().Query<Signature_receipt>(sql).ToList();
        }

        public Orders GetOrd_id(int id)
        {
            string sql = $"SELECT * FROM [dbo].[Orders] where [Ord_id]={id}";
            return myDbContext.Database.GetDbConnection().Query<Orders>(sql).FirstOrDefault();
        }

        public List<Signature_receipt> Sign_condition(string? Sign_time, string? value2, string? Ord_startaddress, string? Ord_destination, int currentPage, int PageSize)
        {
            string sql = "SELECT [Sign_id], A.[Ord_id],[Sign_by],[Sign_time],[Sign_num],[Sign_type],[Sign_phone],[Sign_identy],[Sign_remark],[Sign_rec]" +
                " FROM [dbo].[Signature_receipt] A,[dbo].[Orders] B where A.Ord_id=B.Ord_id";
            if (Sign_time != null && Sign_time != "")
            {
                string[] time1 = Sign_time.Split(',');
                sql += $" and [Sign_time]>='{time1[0]}' and [Sign_time]<='{time1[1]}'";
            }
            if (value2 != null && value2 != "")
            {
                sql += $" AND (Client_name LIKE '%{value2}%')";
            }
            if (Ord_startaddress != null && Ord_startaddress != "")
            {
                sql += $" and [Ord_startaddress] like '%{Ord_startaddress}%'";
            }
            if (Ord_destination != null && Ord_destination != "")
            {
                sql += $" and [Ord_destination] like '%{Ord_destination}%'";
            }
            List<Signature_receipt> list = myDbContext.Database.GetDbConnection().Query<Signature_receipt>(sql).ToList();
            var sign = list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return sign;
        }

        public List<Signature_receipt> GetSign_XQ(int Ord_id)
        {
            string sql = $"select * from [dbo].[Signature_receipt] where Ord_id='{Ord_id}'";
            List<Signature_receipt> list = myDbContext.Database.GetDbConnection().Query<Signature_receipt>(sql).ToList();
            return list;
        }
       
    }
}
